package com.hebeiwangxiaozhang.admin.repo;

import com.hebeiwangxiaozhang.admin.pojo.CommoditySpu;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface CommoditySpuRepository extends JpaRepository<CommoditySpu, Long> {

    @Query(value = "select \"TO DO\" as image, spuinfo.*,sku.id as skuId, sku.price as price, sku.stock as stock,sku.title as skuTitle,sku.own_attr as ownAttr from commodity_sku sku left JOIN\n" +
            "(select \n" +
            "spu.id as spuId, spu.brand_id as brandId, spu.category_id as categoryId,\n" +
            "spudetail.generic_attr as genericAttrs, spudetail.packing_list as packingList, spudetail.customer_service as customerService,\n" +
            "brand.`name` as brandName,\n" +
            "category.`name` as categoryName\n" +
            "from commodity_spu spu, commodity_spu_detail spudetail, commodity_brand brand, commodity_category category \n" +
            "where spu.id =:spuId and spudetail.spu_id=:spuId and spu.brand_id = brand.id and spu.category_id = category.id) as spuinfo on spuinfo.spuId = sku.spu_id", nativeQuery = true)
    List<Map<String, Object>> getCommodityBySpuId(Long spuId);
}
